<?php


namespace app\test\home;


use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Controller;
use think\Db;

class Index extends Controller
{

    /**
     * 获取设置table
     * @return mixed
     */
    public function getTable()
    {
        //$this->assign("id", md5(time()));
        $this->assign("idStr", "dbnahjk");
        return $this->fetch();
    }

    /**
     * 渲染储存table
     * @return mixed
     */

    public function setTable()
    {
        return $this->fetch();
    }

    public function index()
    {
        $spreadsheet = IOFactory::load("C:\Users\guoyunchao\Desktop\德力仪器\提供的表格、记录等\记录、表格\工艺部\AFR-2020年4月V1.0.xls");
        $data = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        print_r($data);die;
        unset($spreadsheet);

//        $data = [
//            ['title1' => '111', 'title2' => '222'],
//            ['title1' => '111', 'title2' => '222'],
//            ['title1' => '111', 'title2' => '222']
//        ];
//        $title = ['第一行标题', '第二行标题'];

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $row = 1; // 从第二行开始
        foreach ($data as $item) {
            $dataCol = 'A';
            foreach ($item as $value) {
                // 单元格内容写入
                $sheet->setCellValue($dataCol . $row, $value);
                dump($dataCol);
                $dataCol++;
            }
            $row++;
        }
        die;

        // Redirect output to a client’s web browser (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="01simple.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        ob_clean();
        ob_start();
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();
        exit;
    }

    public function index0()
    {
        $create_table = "CREATE TABLE `abc` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='系统配置表';";
        //增加字段
        $add_column = "ALTER TABLE `abc`
ADD field2 varchar(255) NOT NULL DEFAULT '' COMMENT '权限分组标识';";
        //删除字段
        $drop_column = "ALTER TABLE `abc`
DROP COLUMN field1;";
        //修改字段属性
        $alter_column = "ALTER TABLE `abc`
MODIFY COLUMN field1 varchar(200);";
        //字段field1改为field2
        $change_column = "ALTER TABLE `abc`
CHANGE field1 field2 varchar(30);";
        //查询表所有的字段及其属性
        $query_table = "SELECT
	`COLUMN_NAME`,
	`COLUMN_TYPE`,
	`IS_NULLABLE`,
	`COLUMN_KEY`,
	`COLUMN_COMMENT`,
	`EXTRA`
FROM
	information_schema. COLUMNS
WHERE
	table_schema = 'deli'
AND table_name = 'abc';";
        Db::query("$add_column");
    }
}